﻿using System;
using System.Collections.Generic;
using SurveySystem.Model;
using System.Data.SqlClient;
using SurveySystem.Utility;

namespace SurveySystem.Repository.MySQL
{
    public class MssqlResponseAnswerRepository : ISurveyResponseAnswerRepository
    {
        public List<ResponseAnswer> GetResponseAnswersForResponse(int responseID)
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;
            SqlDataReader dr;

            List<ResponseAnswer> list = new List<ResponseAnswer>();

            sql = "SELECT `response_id`, `answer_id`, `question_id`, `comment` \n" + //
                  "FROM `survey_response_answer` WHERE response_id=@id";
            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", responseID);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    ResponseAnswer ra = new ResponseAnswer();

                    ra.ResponseID = dr.GetInt32(0);
                    ra.AnswerID = dr.GetInt32(1);
                    ra.QuestionID = dr.GetInt32(2);
                    ra.Comment = dr.GetString(3);

                    list.Add(ra);
                }


                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public List<ResponseAnswer> GetResponseAnswersForQuestion(int questionID)
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;
            SqlDataReader dr;

            List<ResponseAnswer> list = new List<ResponseAnswer>();

            sql = "SELECT `response_id`, `answer_id`, `question_id`, `comment` \n" + //
                  "FROM `survey_response_answer` WHERE question_id=@id";
            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", questionID);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    ResponseAnswer ra = new ResponseAnswer();

                    ra.ResponseID = dr.GetInt32(0);
                    ra.AnswerID = dr.GetInt32(1);
                    ra.QuestionID = dr.GetInt32(2);
                    ra.Comment = dr.GetString(3);

                    list.Add(ra);
                }


                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public List<ResponseAnswer> GetResponseAnswersForAnswer(int answerID)
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;
            SqlDataReader dr;

            List<ResponseAnswer> list = new List<ResponseAnswer>();

            sql = "SELECT `response_id`, `answer_id`, `question_id`, `comment` \n" + //
                  "FROM `survey_response_answer` WHERE answer_id=@id";
            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", answerID);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    ResponseAnswer ra = new ResponseAnswer();

                    ra.ResponseID = dr.GetInt32(0);
                    ra.AnswerID = dr.GetInt32(1);
                    ra.QuestionID = dr.GetInt32(2);
                    ra.Comment = dr.GetString(3);

                    list.Add(ra);
                }


                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public List<ResponseAnswer> GetResponseAnswersForSurvey(int surveyID)
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;
            SqlDataReader dr;

            List<ResponseAnswer> list = new List<ResponseAnswer>();

            sql = "SELECT sra.`response_id`, sra.`answer_id`, sra.`question_id`, sra.`comment` \n" + //
                  "FROM `survey_response_answer` sra \n " + //
                  "INNER JOIN `survey_response` sr ON sr.`response_id` = sra.`response_id`" + //
                  "WHERE sra.`survey_id`=@id";
            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@id", surveyID);

                conn.Open();
                dr = cmd.ExecuteReader();

                while (dr.Read())
                {
                    ResponseAnswer ra = new ResponseAnswer();

                    ra.ResponseID = dr.GetInt32(0);
                    ra.AnswerID = dr.GetInt32(1);
                    ra.QuestionID = dr.GetInt32(2);
                    ra.Comment = dr.GetString(3);

                    list.Add(ra);
                }


                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }

            return list;
        }

        public void AddResponseAnswer(ResponseAnswer ra)
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;

            sql = "INSERT INTO  `survey_response_answer` \n" + //
                  " `response_id`, `question_id`, `answer_id`, `comment` \n" + //
                  "VALUES \n" + //
                  "@response_id, @question_id, @answer_id, @comment)";
            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@response_id", ra.ResponseID);
                cmd.Parameters.AddWithValue("@question_id", ra.QuestionID);
                cmd.Parameters.AddWithValue("@answer_id", ra.AnswerID);
                cmd.Parameters.AddWithValue("@comment", ra.Comment);

                // add the new record to the database
                conn.Open();
                cmd.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }

        }

        public void DeleteResponseAnswer(int responseID, int questionID, int answerID)
        {
            string sql;
            SqlConnection conn;
            SqlCommand cmd;

            sql = "DELETE FROM `survey_response_answer` \n" + //
                  "      WHERE `response_id` = @response_id  \n" + //
                  "        AND `question_id` = @question_id \n" + //
                  "        AND   `answer_id` = @answer_id";
            try
            {
                conn = new SqlConnection(Globals.ConnectionString);
                cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@response_id", responseID);
                cmd.Parameters.AddWithValue("@question_id", questionID);
                cmd.Parameters.AddWithValue("@answer_id", answerID);

                // add the new record to the database
                conn.Open();
                cmd.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException ex)
            {
                // TODO: logging
                throw ex;
            }
        }
    }
}
